﻿using System;
using System.Text;
using System.IO;
using System.Reflection;

using System.Collections.Generic;
using System.Linq;
using System.Web;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;

namespace KingsSharp.Web.Helper
{
    /// <summary>
    /// Excle辅助操作类，需要NOPI类库
    /// </summary>
    public static class ExcleHelper
    {
        #region 生成Excle
        /// <summary>
        /// 创建一个空的工作薄、或带行数据的工作薄。
        /// </summary>
        /// <param name="sheetname"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static HSSFWorkbook Create(string sheetname, params string[] columns)
        {
            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < columns.Length; i++)
            {
                row.CreateCell(i).SetCellValue(columns[i]);
            }
            SetExcleHeadStyle(book, row);
            return book;
        }

        /// <summary>
        /// 最简单的方法添加工作薄数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="book"></param>
        /// <param name="sheetname"></param>
        /// <param name="cells"></param>
        /// <returns></returns>
        public static HSSFWorkbook AppendData(this HSSFWorkbook book, string sheetname, params object[] cells)
        {
            sheetname = string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname;
            ISheet sheet = book.GetSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);
            if (sheet == null) throw new Exception(string.Format("工作薄{0}为空", sheetname));
            int rowsBeginCount = sheet.PhysicalNumberOfRows;
            IRow row = sheet.CreateRow(rowsBeginCount);
            for (int i = 0; i < cells.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(cells[i].ToString());
            }
            return book;
        }

        /// <summary>
        /// 添加工作薄数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="book"></param>
        /// <param name="sheetname">工作薄名称、默认sheet1</param>
        /// <param name="entity">实体</param>
        /// <param name="mapper">单元格生成时提供的回调，可根据需要实现自己的逻辑</param>
        /// <returns></returns>
        public static HSSFWorkbook AppendData<T>(this HSSFWorkbook book, string sheetname,
            T entity, Action<T, ICell, int> mapper = null)
        {
            sheetname = string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname;
            ISheet sheet = book.GetSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);
            if (sheet == null) throw new Exception(string.Format("工作薄{0}为空", sheetname));
            int rowsBeginCount = sheet.PhysicalNumberOfRows;
            int cellsCount = 0;
            IRow row = sheet.CreateRow(rowsBeginCount);
            PropertyInfo[] propertys = entity.GetType().GetProperties();
            if (rowsBeginCount != 0)
            {
                cellsCount = sheet.GetRow(rowsBeginCount - 1).Cells.Count;
            }
            if (cellsCount == 0)
            {
                cellsCount = propertys.Length;
            }
            else
            {
                cellsCount = cellsCount > propertys.Length ? propertys.Length : cellsCount;
            }
            for (int i = 0; i < cellsCount; i++)
            {
                ICell cell = row.CreateCell(i);
                if (mapper != null)
                {
                    mapper(entity, cell, i);
                }
                else
                {
                    var value = propertys[i].GetValue(entity, null);
                    cell.SetCellValue(value == null ? "" : value.ToString());
                }
            }
            return book;
        }
        /// <summary>
        /// 数据的批量添加
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="book"></param>
        /// <param name="sheetname">工作薄名称、默认sheet1</param>
        /// <param name="entitys">实体集合</param>
        /// <param name="mapper">单元格生成时提供的回调，可根据需要实现自己的逻辑</param>
        /// <returns></returns>
        public static HSSFWorkbook AppendListData<T>(this HSSFWorkbook book, string sheetname,
            ICollection<T> entitys, Action<T, ICell, int> mapper = null)
        {
            foreach (var entity in entitys)
            {
                book.AppendData<T>(sheetname, entity, mapper);
            }
            return book;
        }

        /// <summary>
        /// 自动适应宽度
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public static HSSFWorkbook WithToAuto(this HSSFWorkbook book)
        {
            SetWithAuto(book);
            return book;
        }
        /// <summary>
        /// 根据实体创建Excle模版、常用于提供导入数据的模版
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="headnames"></param>
        /// <returns></returns>
        public static HSSFWorkbook Create<T>(T entity, string sheetname = "sheet1", string[] headnames = null)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);

            IRow row = sheet.CreateRow(0);
            IRow row1 = sheet.CreateRow(1);

            PropertyInfo[] propertys = entity.GetType().GetProperties();

            for (int i = 0; i < propertys.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                if (headnames != null && headnames.Length > i)
                {
                    cell.SetCellValue(headnames[i]);
                }
                else
                {
                    cell.SetCellValue(propertys[i].Name);
                }
                ICell cell1 = row1.CreateCell(i);
                cell1.SetCellValue(propertys[i].GetValue(entity, null).ToString());
            }
            SetExcleHeadStyle(book, row);
            SetWithAuto(book, propertys.Length);
            sheet.ForceFormulaRecalculation = true;
            return book;
        }

        /// <summary>
        /// 根据强类型集合创建Excle
        /// </summary>
        /// <param name="entitys">集合数据</param>
        /// <param name="sheetname">工作薄名称</param>
        /// <param name="sheetname">头部列名映射数组、不提供时将默认实体属性</param>
        /// <param name="hashead">首行是否包含列名</param>
        /// <returns>NOPI 工作薄</returns>
        public static HSSFWorkbook CreateList(ICollection<object> entitys, string sheetname = "sheet1", string[] headnames = null, bool hashead = true)
        {
            if (entitys.Count() == 0)
            {
                throw new Exception("集合中没有任何数据");
            }
            HSSFWorkbook book = Create(entitys.First(), sheetname, headnames);
            ISheet sheet = book.GetSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);
            PropertyInfo[] propertys = entitys.First().GetType().GetProperties();
            int j = 2;
            foreach (var entity in entitys)
            {
                IRow row = sheet.CreateRow(j);
                for (int i = 0; i < propertys.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(propertys[i].GetValue(entity, null).ToString());
                }
                j++;
            }
            return book;
        }

        /// <summary>
        /// 根据数组创建Excle
        /// </summary>
        /// <param name="headnames"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static HSSFWorkbook Create(string[] headnames, string sheetname = "sheet1")
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet(string.IsNullOrWhiteSpace(sheetname) ? "sheet1" : sheetname);
            sheet.ForceFormulaRecalculation = true;
            IRow row = sheet.CreateRow(0);

            for (int i = 0; i < headnames.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(headnames[i]);
            }
            SetExcleHeadStyle(book, row);
            return book;
        }
        #endregion

        #region 读取Excle

        /// <summary>
        /// 读取Excle字节流，返回强类型集合、Excle表格必须为规则表格，
        /// 当有多个头部时，离数据行最近的一个表头必须与数据行一一对应
        /// </summary>
        /// <typeparam name="T">返回的类型</typeparam>
        /// <param name="path">文件路径</param>
        /// <param name="datacolumns">Excle列与属性的映射</param>
        /// <param name="headcolum">头部是否包含列名</param>
        /// <param name="checksheet">sheet检测委托</param>
        /// <param name="headcolum">头部包含多行列名时，指明具体几行</param>
        /// <returns></returns>
        public static ICollection<T> ReadToList<T>(string path,
            string[] datacolumns = null, Func<ISheet, bool> checksheet = null,
            bool headcolum = true, int headcolumcount = 1)
            where T : new()
        {
            Stream npoistream = File.Open(path, FileMode.Open, FileAccess.Read);

            List<T> entitys = new List<T>();
            HSSFWorkbook book = null;
            try
            {
                book = new HSSFWorkbook(npoistream);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            int sheetsCount = book.NumberOfSheets;
            for (int i = 0; i < sheetsCount; i++)
            {

                ISheet sheet = book.GetSheetAt(i);
                if (checksheet != null && checksheet(sheet) == false)
                {
                    continue;
                }
                int rowCount = sheet.PhysicalNumberOfRows;
                int beginCount = headcolum ? headcolumcount : 0;
                for (var j = beginCount; j < rowCount; j++)
                {
                    IRow row = sheet.GetRow(j);
                    T entity = ReadRow<T>(row, datacolumns);
                    entitys.Add(entity);
                }
            }
            return entitys;
        }

        /// <summary>
        /// 读取Excle字节流，返回强类型集合、Excle表格必须为规则表格，
        /// 当有多个头部时，离数据行最近的一个表头必须与数据行一一对应
        /// </summary>
        /// <typeparam name="T">返回的类型</typeparam>
        /// <param name="npoistream">字节流</param>
        /// <param name="datacolumns">Excle列与属性的映射</param>
        /// <param name="checksheet">sheet检测委托</param>
        /// <param name="headcolum">头部是否包含列名</param>
        /// <param name="headcolum">头部包含多行列名时，指明具体几行</param>
        /// <returns></returns>
        public static ICollection<T> ReadToList<T>(Stream npoistream,
            string[] datacolumns = null, Func<ISheet, bool> checksheet = null,
            bool headcolum = true, int headcolumcount = 1)
            where T : new()
        {
            List<T> entitys = new List<T>();
            XSSFWorkbook book = null;
            try
            {
                book = new XSSFWorkbook(npoistream);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            int sheetsCount = book.NumberOfSheets;
            for (int i = 0; i < sheetsCount; i++)
            {
                ISheet sheet = book.GetSheetAt(i);
                if (checksheet != null && checksheet(sheet) == false)
                {
                    continue;
                }
                int rowCount = sheet.PhysicalNumberOfRows;
                int beginCount = headcolum ? headcolumcount : 0;
                for (var j = beginCount; i < rowCount; beginCount++)
                {
                    IRow row = sheet.GetRow(j);
                    T entity = ReadRow<T>(row, datacolumns);
                    entitys.Add(entity);
                }
            }
            return entitys;
        }

        #endregion

        #region HSSFWorkbook扩展方法
        /// <summary>
        /// 返回workbook文件流
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public static MemoryStream ToStream(this HSSFWorkbook book, bool autoSize = false)
        {
            if (autoSize)
            {
                book.WithToAuto();
            }
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }
        /// <summary>
        /// 添加一个工作表格
        /// </summary>
        /// <param name="book"></param>
        /// <param name="sheetname"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static HSSFWorkbook CreateSheet(this HSSFWorkbook book, string sheetname, params string[] columns)
        {
            if (string.IsNullOrWhiteSpace(sheetname))
            {
                throw new Exception("创建sheet的名称不能为空");
            }
            ISheet sheet = book.CreateSheet(sheetname);
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < columns.Length; i++)
            {
                row.CreateCell(i).SetCellValue(columns[i]);
            }
            SetExcleHeadStyle(book, row);
            return book;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 设置表头格式
        /// </summary>
        /// <param name="book"></param>
        /// <param name="row"></param>
        private static void SetExcleHeadStyle(HSSFWorkbook book, IRow row)
        {
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            IFont font = book.CreateFont();
            font.Boldweight = short.MaxValue;
            font.Color = new NPOI.HSSF.Util.HSSFColor.Red().Indexed;
            style.SetFont(font);
            row.Cells.ForEach(cell => cell.CellStyle = style);
        }
        /// <summary>
        /// 自动适应宽度支持中文
        /// </summary>
        /// <param name="book"></param>
        /// <param name="columcount"></param>
        private static void SetWithAuto(HSSFWorkbook book, int columcount = 25)
        {
            int sheetcount = book.NumberOfSheets;
            for (int j = 0; j < sheetcount; j++)
            {
                ISheet sheet = book.GetSheetAt(j);

                for (int i = 0; i < columcount; i++)
                {
                    int columnWidth = sheet.GetColumnWidth(i) / 256;//获取当前列宽度
                    for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow = sheet.GetRow(rowNum);
                        if (currentRow == null) continue;
                        ICell currentCell = currentRow.GetCell(i);
                        if (currentCell == null) continue;
                        int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
                    }
                    sheet.SetColumnWidth(i, columnWidth * 256);
                }
            }
        }

        /// <summary>
        /// 根据行与字符串数组映射返回对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <param name="datacolumns"></param>
        /// <returns></returns>
        private static T ReadRow<T>(IRow row, string[] datacolumns = null)
            where T : new()
        {
            T entity = new T();
            PropertyInfo[] propertys = entity.GetType().GetProperties();
            ICell[] crr = row.Cells.ToArray();
            if (datacolumns != null && datacolumns.Length > 0)
            {
                for (int i = 0; i < datacolumns.Length; i++)
                {
                    string proname = datacolumns[i];
                    var pro = propertys.Where(a => a.Name.ToLower() == proname.ToLower()).FirstOrDefault();
                    if (pro != null && crr.Length > i)
                    {
                        pro.SetValue(entity, Convert.ChangeType(crr[i].ToString(), pro.PropertyType), null);
                    }
                }
            }
            else
            {
                for (int i = 0; i < crr.Length; i++)
                {
                    propertys[i].SetValue(entity, crr[i].StringCellValue, null);
                }
            }
            return entity;
        }
        #endregion
    }
}